package com.example.managemyself.config;

import android.content.Context;
import android.util.Log;
import com.example.managemyself.data.model.User;
import com.example.managemyself.util.MySQLConnectionHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class DatabaseInitializer {
    private static final String TAG = "DatabaseInitializer";
    private static DatabaseInitializer instance;
    private MySQLConnectionHelper connectionHelper;
    private AppConfig appConfig;
    
    private DatabaseInitializer() {
        connectionHelper = MySQLConnectionHelper.getInstance();
        appConfig = AppConfig.getInstance();
    }
    
    public static synchronized DatabaseInitializer getInstance() {
        if (instance == null) {
            instance = new DatabaseInitializer();
        }
        return instance;
    }
    
    public void initializeDatabase(Context context) {
        Log.i(TAG, "开始初始化数据库...");
        try {
            createTables();
            insertDefaultData();
            Log.i(TAG, "数据库初始化完成");
        } catch (Exception e) {
            Log.e(TAG, "数据库初始化失败", e);
        }
    }
    
    private void createTables() throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = connectionHelper.getConnection();
            stmt = conn.createStatement();
            createUsersTable(stmt);
            createTasksTable(stmt);
            Log.i(TAG, "数据库表创建完成");
        } finally {
            closeResources(conn, stmt, null);
        }
    }
    
    private void createUsersTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS users (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "username VARCHAR(50) NOT NULL UNIQUE," +
                "email VARCHAR(100) NOT NULL UNIQUE," +
                "password VARCHAR(255) NOT NULL," +
                "nickname VARCHAR(50)," +
                "avatar VARCHAR(255)," +
                "role VARCHAR(20) NOT NULL DEFAULT 'USER'," +
                "created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "last_login_date TIMESTAMP NULL," +
                "is_active BOOLEAN DEFAULT TRUE" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        stmt.executeUpdate(sql);
        Log.d(TAG, "用户表创建完成");
    }
    
    private void createTasksTable(Statement stmt) throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS tasks (" +
                "id INT AUTO_INCREMENT PRIMARY KEY," +
                "title VARCHAR(200) NOT NULL," +
                "description TEXT," +
                "status VARCHAR(20) DEFAULT '待完成'," +
                "importance INT DEFAULT 0," +
                "urgency VARCHAR(20)," +
                "repeat_type VARCHAR(20)," +
                "type VARCHAR(50)," +
                "due_date TIMESTAMP NULL," +
                "created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "completed_date TIMESTAMP NULL," +
                "is_completed BOOLEAN DEFAULT FALSE," +
                "user_id INT" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        stmt.executeUpdate(sql);
        Log.d(TAG, "任务表创建完成");
    }
    
    private void insertDefaultData() throws SQLException {
        insertDefaultAdmin();
        insertDefaultUser();
        Log.i(TAG, "默认数据插入完成");
    }
    
    private void insertDefaultAdmin() throws SQLException {
        String username = appConfig.getDefaultAdminUsername();
        String password = appConfig.getDefaultAdminPassword();
        String email = appConfig.getDefaultAdminEmail();
        
        if (userExists(username)) {
            Log.d(TAG, "默认管理员用户已存在，跳过创建");
            return;
        }
        
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = connectionHelper.getConnection();
            String sql = "INSERT INTO users (username, email, password, nickname, role, created_date, is_active) VALUES (?, ?, ?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, email);
            stmt.setString(3, password);
            stmt.setString(4, "系统管理员");
            stmt.setString(5, User.ROLE_ADMIN);
            stmt.setTimestamp(6, new java.sql.Timestamp(new Date().getTime()));
            stmt.setBoolean(7, true);
            
            int result = stmt.executeUpdate();
            if (result > 0) {
                Log.i(TAG, "默认管理员用户创建成功: " + username);
            }
        } finally {
            closeResources(conn, stmt, null);
        }
    }
    
    private void insertDefaultUser() throws SQLException {
        String username = appConfig.getDefaultUserUsername();
        String password = appConfig.getDefaultUserPassword();
        String email = appConfig.getDefaultUserEmail();
        
        if (userExists(username)) {
            Log.d(TAG, "默认普通用户已存在，跳过创建");
            return;
        }
        
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = connectionHelper.getConnection();
            String sql = "INSERT INTO users (username, email, password, nickname, role, created_date, is_active) VALUES (?, ?, ?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, email);
            stmt.setString(3, password);
            stmt.setString(4, "测试用户");
            stmt.setString(5, User.ROLE_USER);
            stmt.setTimestamp(6, new java.sql.Timestamp(new Date().getTime()));
            stmt.setBoolean(7, true);
            
            int result = stmt.executeUpdate();
            if (result > 0) {
                Log.i(TAG, "默认普通用户创建成功: " + username);
            }
        } finally {
            closeResources(conn, stmt, null);
        }
    }
    
    private boolean userExists(String username) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = connectionHelper.getConnection();
            String sql = "SELECT COUNT(*) FROM users WHERE username = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            closeResources(conn, stmt, rs);
        }
    }
    
    public boolean testConnection() {
        Connection conn = null;
        try {
            conn = connectionHelper.getConnection();
            return conn != null && !conn.isClosed();
        } catch (SQLException e) {
            Log.e(TAG, "数据库连接测试失败", e);
            return false;
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Log.e(TAG, "关闭连接失败", e);
                }
            }
        }
    }
    
    private void closeResources(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            Log.e(TAG, "关闭数据库资源失败", e);
        }
    }
} 